set hive.exec.dynamic.partition=true;
set hive.exec.dynamic.partition.mode='nonstrict';
set hive.exec.max.dynamic.partitions=400;
set hive.exec.max.dynamic.partitions.pernode=400;

insert overwrite table jms_dm.dm_whole_effect_tuji_summary_dt
select
     '时效签收' as date_type  --查询维度
    ,dim_start.virt_code  as start_virt_code 
    ,dim_start.virt_name  as start_virt_name 
    ,base.send_agent_code as start_agent_code 
    ,base.send_agent_name as start_agent_name 
    ,dim_end.virt_code    as end_virt_code   
    ,dim_end.virt_name    as end_virt_name   
    ,base.sign_agent_code as end_agent_code 
    ,base.sign_agent_name as end_agent_name 
    ,case when base.ordersource_name like '%桃花岛%' then '桃花岛'
          when base.ordersource_name = '紫金山' then '紫金山'
          when base.ordersource_name = '逍遥峰' then '逍遥峰'
          when base.ordersource_name = '极地湾' then '极地湾'
          when base.ordersource_name = '七星潭' then '七星潭'
          else '其他' end as ordersource_name
    ,sum(base.first_center_taking_time        ) as first_center_taking_time         --揽收—入首中心时长
    ,sum(base.inout_first_center_time         ) as inout_first_center_time          --入首—出首中心时长
    ,sum(base.last_first_center_time          ) as last_first_center_time           --出首—入末中心时长
    ,sum(base.inout_last_center_time          ) as inout_last_center_time           --入末—出末中心时长
    ,sum(base.dest_last_center_time           ) as dest_last_center_time            --出末中心—网点到件时长
    ,sum(base.end_network_arrival_deliver_time) as end_network_arrival_deliver_time --网点到件—网点出仓时长
    ,sum(base.deliver_aging_sign_time         ) as deliver_aging_sign_time          --网点出仓—时效签收仓时长
    ,sum(base.first_center_taking_cnt         ) as first_center_taking_cnt          --揽收—入首中心票件量
    ,sum(base.inout_first_center_cnt          ) as inout_first_center_cnt           --入首—出首中心票件量
    ,sum(base.last_first_center_cnt           ) as last_first_center_cnt            --出首—入末中心票件量
    ,sum(base.inout_last_center_cnt           ) as inout_last_center_cnt            --入末—出末中心票件量
    ,sum(base.dest_last_center_cnt            ) as dest_last_center_cnt             --出末中心—网点到件票件量
    ,sum(base.end_network_arrival_deliver_cnt ) as end_network_arrival_deliver_cnt  --网点到件—网点出仓票件量
    ,sum(base.deliver_aging_sign_cnt          ) as deliver_aging_sign_cnt           --网点出仓—时效签收仓票件量
    ,dt
from jms_dm.dm_duration_of_whole_process_pre_dt base 
left join (
    select
         agent_code
        ,agent_name
        ,virt_code
        ,virt_name
    from jms_dim.dim_network_whole_massage
    group by agent_code
            ,agent_name
            ,virt_code
            ,virt_name
) dim_start on dim_start.agent_code=base.send_agent_code
left join (
    select
         agent_code
        ,agent_name
        ,virt_code
        ,virt_name
    from jms_dim.dim_network_whole_massage
    group by agent_code
            ,agent_name
            ,virt_code
            ,virt_name
) dim_end on dim_end.agent_code = base.sign_agent_code
where dt between date_sub('{{ execution_date | cst_ds }}',10) and '{{ execution_date | cst_ds }}'
group by  dim_start.virt_code  
        ,dim_start.virt_name  
        ,base.send_agent_code 
        ,base.send_agent_name 
        ,dim_end.virt_code    
        ,dim_end.virt_name    
        ,base.sign_agent_code 
        ,base.sign_agent_name 
        ,case when base.ordersource_name like '%桃花岛%' then '桃花岛'
              when base.ordersource_name = '紫金山' then '紫金山'
              when base.ordersource_name = '逍遥峰' then '逍遥峰'
              when base.ordersource_name = '极地湾' then '极地湾'
              when base.ordersource_name = '七星潭' then '七星潭'
              else '其他' end 
        ,base.dt
union all 
select
     '揽收' as date_type  --查询维度
    ,dim_start.virt_code  as start_virt_code 
    ,dim_start.virt_name  as start_virt_name 
    ,base.send_agent_code as start_agent_code 
    ,base.send_agent_name as start_agent_name 
    ,dim_end.virt_code    as end_virt_code   
    ,dim_end.virt_name    as end_virt_name   
    ,base.sign_agent_code as end_agent_code 
    ,base.sign_agent_name as end_agent_name 
    ,case when base.ordersource_name like '%桃花岛%' then '桃花岛'
          when base.ordersource_name = '紫金山' then '紫金山'
          when base.ordersource_name = '逍遥峰' then '逍遥峰'
          when base.ordersource_name = '极地湾' then '极地湾'
          when base.ordersource_name = '七星潭' then '七星潭'
          else '其他' end as ordersource_name
    ,sum(base.first_center_taking_time        ) as first_center_taking_time         --揽收—入首中心时长
    ,sum(base.inout_first_center_time         ) as inout_first_center_time          --入首—出首中心时长
    ,sum(base.last_first_center_time          ) as last_first_center_time           --出首—入末中心时长
    ,sum(base.inout_last_center_time          ) as inout_last_center_time           --入末—出末中心时长
    ,sum(base.dest_last_center_time           ) as dest_last_center_time            --出末中心—网点到件时长
    ,sum(base.end_network_arrival_deliver_time) as end_network_arrival_deliver_time --网点到件—网点出仓时长
    ,sum(base.deliver_aging_sign_time         ) as deliver_aging_sign_time          --网点出仓—时效签收仓时长
    ,sum(base.first_center_taking_cnt         ) as first_center_taking_cnt          --揽收—入首中心票件量
    ,sum(base.inout_first_center_cnt          ) as inout_first_center_cnt           --入首—出首中心票件量
    ,sum(base.last_first_center_cnt           ) as last_first_center_cnt            --出首—入末中心票件量
    ,sum(base.inout_last_center_cnt           ) as inout_last_center_cnt            --入末—出末中心票件量
    ,sum(base.dest_last_center_cnt            ) as dest_last_center_cnt             --出末中心—网点到件票件量
    ,sum(base.end_network_arrival_deliver_cnt ) as end_network_arrival_deliver_cnt  --网点到件—网点出仓票件量
    ,sum(base.deliver_aging_sign_cnt          ) as deliver_aging_sign_cnt           --网点出仓—时效签收仓票件量
    ,dt
from jms_dm.dm_duration_of_whole_process_dt base 
left join (
    select
         agent_code
        ,agent_name
        ,virt_code
        ,virt_name
    from jms_dim.dim_network_whole_massage
    group by agent_code
            ,agent_name
            ,virt_code
            ,virt_name
) dim_start on dim_start.agent_code=base.send_agent_code
left join (
    select
         agent_code
        ,agent_name
        ,virt_code
        ,virt_name
    from jms_dim.dim_network_whole_massage
    group by agent_code
            ,agent_name
            ,virt_code
            ,virt_name
) dim_end on dim_end.agent_code = base.sign_agent_code
where dt between date_sub('{{ execution_date | cst_ds }}',10) and '{{ execution_date | cst_ds }}'
group by dim_start.virt_code  
        ,dim_start.virt_name  
        ,base.send_agent_code 
        ,base.send_agent_name 
        ,dim_end.virt_code    
        ,dim_end.virt_name    
        ,base.sign_agent_code 
        ,base.sign_agent_name 
        ,case when base.ordersource_name like '%桃花岛%' then '桃花岛'
              when base.ordersource_name = '紫金山' then '紫金山'
              when base.ordersource_name = '逍遥峰' then '逍遥峰'
              when base.ordersource_name = '极地湾' then '极地湾'
              when base.ordersource_name = '七星潭' then '七星潭'
              else '其他' end 
        ,base.dt
distribute by dt,pmod(hash(rand()),10)
;
